Shareware Grab Bag/Shareware Grab Bag.iso/003/pcdbms.arc/MANUAL.2

Text File | 1985-09-13 | 55KB | 2,444 lines

                        CHAPTER 5

                    COMMAND REFERENCE



OVERVIEW

The commands summarized in Table 5-1 are  implemented  in
PC-DBMS.  Subsequent sections document them.

COMMAND         FUNCTION

DEFINE          invokes define utility to update schema
DEKEY           removes an index
DISPLAY         invokes display utility to update
                    a table
EXIT            terminates execution of PC-DBMS
KEY             builds an index
OPEN            makes available an existing or new
                    data base
OUTPUT          redirects output to file or printer
REMOVE          removes table from the data base
SELECT          retrieves records from one or two tables
SHOW            displays portion of schema
TOLERANCE       sets tolerance used for numeric
                    comparisons

Table 5-1.  Summary of PC-DBMS commands.





                           5-1



















                    COMMAND REFERENCE
                 NOTATIONAL CONVENTIONS


NOTATIONAL CONVENTIONS

Syntax  forms  for  the  commands   use   the   following
notational conventions.

      -  Lower  case  words  indicate  that  you   should
         substitute a word or value of your choice.

      -  Upper case words are keywords and they should be
         used  where  shown.   However,  keywords  can be
         truncated to three or more characters and can be
         entered in upper or lower case.

      -  Brackets ([]) indicate optional elements.

      -  Ellipses (...) indicate  the  preceding  item(s)
         may be repeated one or more times.




DEFINE

The define command is issued after the  open  command  is
given  for  a  new or existing data base.  It invokes the
define utility to enter definitions of tables  and  their
fields.  The syntax is:

     DEFINE

The define utility is conversational.   It  presents  two
data   entry  screens.   The  first  screen  accepts  the
description  of  a  table.   The  second  screen  accepts
descriptions of that table's fields.

The first or table screen prompts for the  table's  name,
the  specification  of the file containing it, the record
termination character and an optional verbal description.




                           5-2



















                    COMMAND REFERENCE
                         DEFINE


When that screen is completed,  the  second  screen  type
appears for entry of field data -- field name, data type,
field width, indexing and an optional verbal description.
When completed, the screen reappears blank for definition
of the next field.  If there is no  "next"  field,  press
F10  and  the  first  data  entry screen will reappear to
define the next table.   Then,  if  there  is  no  "next"
table, press F10 to exit the define utility.

File specifications may not include path names but  drive
names  may  be  used.  If a drive name is given, the file
must always be on that disk drive.  If no drive  name  is
given,  the  file  must  be on the logged (default) drive
which you can set at the DOS level.

The table screen prompts for a number to describe the end
of  record  terminator.  Enter 0 for no terminator, 1 for
carriage return and 2 for carriage return-line feed.  The
choice  of  2  facilitates  processing  the  file outside
PC-DBMS.

Fields may have one of three data types:  text, number or
filler.   Filler  fields  may contain anything -- blanks,
numbers  or   text.    The   indexing   and   description
information  is  not solicited for filler fields.  Filler
fields can be displayed with the select command  but  may
not  be  used  in  where  or  sort  clauses.  The display
utility ignores them;  on input, they are  blank  filled.
Filler fields are useful in masking columns of extraneous
material in imported files or to  insert  blanks  between
fields during input.

If a field is more than 141 characters wide, it cannot be
indexed so the index prompt will not appear.

If the data base is to include a  table  in  an  imported
file,  its  definition  must  exactly  describe the file.
Filler fields of any desired  width  can  be  used.   Any
record  whose  first character is a backslash (\) will be
considered deleted.


                           5-3



















                    COMMAND REFERENCE
                          DEKEY


DEKEY

The dekey command updates the schema so that it shows the
named  field as not keyed.  It has no effect on the index
file but the old index will never be usable.  The  syntax
is:

     DEKEY qfn

Qfn is  a  qualified  field  name.   For  example,  DEKEY
OPUS.WORK  changes the schema so that field WORK in table
OPUS is regarded as not keyed.  When a field is  indexed,
it  is necessary to maintain the indices when records are
added,  changed  or  deleted.    Dekeying   avoids   this
overhead.



DISPLAY

The display command projects a data  entry  form  on  the
screen.   The  form consists of background text and field
blocks corresponding to non-filler  fields  in  the  data
table.  The syntax is:

DISPLAY [\INPUT] table_name [WHERE    ]
         \QUERY
         \EDIT

The display form operates on  a  single  table  given  by
table_name.   That  table may have up to 60 fields.  Some
tables may  be  rejected  because  all  their  non-filler
fields cannot be squeezed onto the screen.

A form  will  be  created  dynamically.   Display  fields
corresponding   to  data  fields  appear  as  rectangular
blocks;  they are one or more characters wide and one  or
more lines high.




                           5-4



















                    COMMAND REFERENCE
                         DISPLAY


The display command has  four  forms.   With  the  \QUERY
qualifier,  a  select  command is composed for use as the
next command.  If \INPUT is specified,  new  records  are
entered  and  appended  to  the table.  The default \EDIT
qualifier permits  retrieval,  editing  and  deletion  of
existing  records.   The where clause form of the command
also retrieves records for editing  and  deletion,  those
satisfying  the where clause.  Screen layouts are similar
for all forms;  only the bottom information lines differ.
The various forms are discussed below.

An example of the \INPUT form is

     DISPLAY\INPUT COMP

In this example, a form will be projected  with  a  blank
field  for  each non-filler data field in the COMP table.
Next to each field, its  name  will  be  shown.   At  the
bottom  right  of  the screen, INPUT will be displayed to
identify the function.  The rest of the bottom line gives
other useful information.

Fill in all fields (or leave some blank) and press F7  to
store  the  new  record.   The  form reappears with blank
fields to enter another record.  Press F10  to  exit  the
display  utility.   However,  exiting  does not store any
record still on screen.

The \QUERY form of  the  display  command  helps  compose
certain  select  commands.   The  form shows QUERY at the
bottom right and has blank fields.  Fill in up to ten  of
them  and  press  F7.  This exits the display utility and
restores the main screen.   A  select  command  resulting
from  the  QUERY  form  entries  will  be  in the command
window.  Edit, erase or execute it.   It  will  have  AND
connectives,  no parentheses, no negations, = comparisons
and ALL in its display list.

For example, suppose you invoke the display utility with

     DISPLAY\QUERY COMP

                           5-5



















                    COMMAND REFERENCE
                         DISPLAY


You enter 1770 in the BORN field and  Beethoven,  L.   in
the  NAME  field.   After  pressing  F7,  the main screen
reappears with the following in the command window:

     SELECT  COMP.ALL  WHERE  NAME="Beethoven,  L."   AND
     BORN=1770

The command  can  be  edited  if  it's  unsuitable.   For
example,  only  certain  fields  might  be desired in the
output.

The \EDIT form of the display command is used to find and
edit  or  delete  records.  Two phases repeat in a cycle.
First, a screen query is  executed.   Second,  qualifying
records are displayed one after the other on the form for
editing and deletion.  When the editing and deletion  are
complete,  the blank query screen reappears to repeat the
process.

For example, to edit  records  in  the  COMP  table,  the
command is

     DISPLAY\EDIT COMP

The query phase of the cycle is almost identical  to  the
DISPLAY\QUERY function.  A form appears with QUERY at the
bottom right and blank fields.  Fill in up to ten  fields
and  press F7 to execute the query.  Alternatively, press
F10 to exit from the display utility.

If  F7  was  pressed,  all  the  qualifying  records  are
displayed  one  at  a  time on the form.  The bottom line
will now show editing prompts and EDIT at the right.

As each qualifying record is displayed, do one  of  three
things:   leave  it  unchanged, edit it or delete it.  To
leave it unchanged, press  F7.   To  edit  it,  make  the
desired  changes  on  screen and press F6.  To delete it,
press F4.  In any case, the next qualifying  record  will
be  displayed.   However,  to return to the first (query)
phase, press F10.  It is not possible to exit the display

                           5-6



















                    COMMAND REFERENCE
                         DISPLAY


utility directly from the edit screen.

After all qualifying records  have  been  displayed,  the
screen will clear and the following message will appear:

        NO (MORE) RECORDS QUALIFY

                            Press any key to continue

After pressing a key, the query screen reappears to begin
the  query-edit  cycle anew.  Alternatively, press F10 to
exit the display utility.

DISPLAY\EDIT is the default.  That is,  if  there  is  no
where  clause  and  no  qualifier  (\),  then  the  \EDIT
qualifier is assumed.

The last form of the display command is the only one that
uses  the  where clause.  Any qualifier is ignored.  This
form of the command retrieves for  editing  and  deletion
records which satisfy the where clause.

The where clause is documented under the select  command.
The  default table name at the beginning of the clause is
table_name.  A second table  can  be  referenced  in  the
clause.

Qualifying records are projected on the same edit  screen
used  with the \EDIT form of the command.  However, after
the last record has been processed, the  display  utility
is exited.

For example, one way to retrieve Beethoven's  record  for
editing is with this command:

     DISPLAY COMP WHERE NAME $ "Beethoven"

When text fields  are  entered  or  displayed  on  screen
forms,  quote  marks  are treated as ordinary characters;
they are not string delimiters.  Hence, use one quote for
each quote mark you wish to embed in the record.

                           5-7



















                    COMMAND REFERENCE
                         DISPLAY


Numbers are entered according to the rules for numbers in
commands.    See  COMMAND  FORMATTING  AND  EXECUTION  in
chapter 4.

The  PC-DBMS  editor  operates  in  each   field   block.
However, only replace mode is available.  See THE PC-DBMS
EDITOR in chapter 4.

To move the cursor forwards from one field to  the  next,
press tab.  Press F9 to move it backwards.

A help screen is available by pressing F1.

Whenever records are added, deleted or  modified  by  the
display   utility,   the   corresponding  index  file  is
automatically updated.  However, if more than  13  fields
are  indexed,  only  the  first  13 will be updated.  The
indices on the other  fields  will  be  incorrect.   They
should  be dekeyed and then rebuilt with the key command.
In practice, few tables have more than 13 keys.



EXIT

The exit command  terminates  execution  of  PC-DBMS  and
returns  the user to the operating system.  The command's
syntax is EXIT but it can be issued by pressing F10.



KEY

The key command builds an index for the  specified  field
and  updates the schema to reflect that fact.  The syntax
is:

     KEY qfn




                           5-8



















                    COMMAND REFERENCE
                           KEY


Qfn  is  a  qualified  field  name.   For  example,   KEY
OPUS.WORK builds an index for the field WORK in the table
OPUS.  The index  will  be  updated  when  the  table  is
altered by the display command.

Indexing will be aborted and an error  message  displayed
if  an  attempt  is  made  to  key a field wider than 141
characters.



OPEN

Open is typically the first command the user  gives.   It
makes  an existing data base available.  If the data base
does not exist, its schema file  will  be  created.   The
command syntax is:

     OPEN dbname

Dbname is the name of the existing or new data base.  For
example, OPEN MUSIC.

There is no close command.  An open  data  base  will  be
closed when the exit or open command is given.



OUTPUT

The output command is used to redirect the output of  the
show and select commands to a file or to a device such as
a printer.  The syntax is:

     OUTPUT [file-spec]

File-spec is  the  name  of  the  file  or  device.   For
example,  OUTPUT FILE.DAT sends output to a disk file and
OUTPUT  LPT1:    prints   the   output.    If   no   file
specification  is  given,  the  screen  default  will  be
restored.

                           5-9



















                    COMMAND REFERENCE
                         OUTPUT


If file-spec is a disk file which already exists, it will
be deleted and a new file of the same name created.

The redirected output will include  column  headings.   A
file  created by this command will not become part of the
data base.  That function is served by the "into"  clause
of the select command.



REMOVE

The remove command marks as deleted the schema definition
of the named table(s).  The syntax is:

     REMOVE table_name [...]

For example, REMOVE CUSTOMERS BALANCES  removes  the  two
named  tables.   The  data  files  are unaffected by this
command but they can no longer be accessed.



SELECT

The select command retrieves data from the tables.  It is
capable  of  gathering  data  from  one  or  two  tables,
filtering and sorting the retrieved  records,  displaying
up  to  12  of their fields in any order, and writing the
result to a new table which becomes a part  of  the  data
base.   The  output  ordinarily  is written to the bottom
half of the screen or whatever target is specified by the
output  command.   However, output will be written to the
designated new table if the into clause is used.

The abbreviated syntax is:

     SELECT qfn's [WHERE    ] [SORTED BY    ]
        [INTO new_table_name]



                          5-10



















                    COMMAND REFERENCE
                         SELECT


The first clause, qfn's, is the only necessary  one.   It
is  a list of qualified field names to be displayed.  The
fields can come from one or two tables.  An example of  a
two   table   retrieval  is  SELECT  COMP.NAME  COMP.BORN
OPUS.WORK.  If more than  twelve  fields  are  specified,
additional  ones  will  be  ignored and the query will be
executed.  The display will be truncated  to  accommodate
screen width.

To display all fields of a table, two special  qfn's  can
be  used.  The table name and period are as usual but the
field name is either ALL or ALLF.   For  example,  SELECT
COMP.ALL  displays  all  non-filler  fields  of  the COMP
table.  ALLF is used when  the  filler  fields  are  also
desired.

Each time a table name is encountered (in  a  qfn),  that
table  becomes the default.  Subsequent field names which
omit the table name  and  period  reference  the  default
table.   For  example,  SELECT COMP.NAME COUNTRY OPUS.ALL
displays two fields from COMP and all fields from OPUS.

Figure 5-1 is an example of a simple select command.



















                          5-11



















                    COMMAND REFERENCE
                         SELECT



        SELECT COMP.ALL


NAME                 BORN  DIED  COUNTRY

Tchaikovsky, P.      1840  1893  Russia
Brahms, J.           1833  1897  Germany
Verdi, G.            1813  1901  Italy
Handel, G.           1685  1759  England
Mozart, W.           1756  1791  Austria
Bach, J.             1685  1750  Germany
Chopin, F.           1810  1849  France
Beethoven, L.        1770  1827  Germany
Haydn, F.            1732  1809  Austria
Mendelssohn, F.      1809  1847  Germany
Debussy, C.          1862  1918  France

Figure 5-1.  A simple select command.

The where clause is optional.  A  where  clause  contains
one to ten comparisons (conditions) connected by ANDs and
ORs.  A record  must  satisfy  the  where  clause  to  be
displayed.  For example, attaching the where clause WHERE
BORN>1799 to the preceding query would eliminate from the
display the five composers born before 1800.

The eight comparisons are shown in Table 5-2.

        COMPARISON                REPRESENTATION

        less than                       <
        less than or equal to           <=
        equal to                        =
        greater than or equal to        >=
        greater than                    >
        unequal to                      <>
        contains                        $
        does not contain                %

Table 5-2.  The eight comparison operators.

                          5-12



















                    COMMAND REFERENCE
                         SELECT


On one side of the comparison there must be a field name,
possibly  qualified.   On  the  other  side, there may be
another field name, possibly qualified,  or  a  constant,
numeric  or text as appropriate.  The elements on the two
sides of the comparison must be of the  same  data  type,
both numeric or both text.  Filler fields may not be used
in comparisons.  All eight operators  can  be  used  with
text  elements.   Only  the  first  six  can be used with
numeric elements.  Spaces before and after  the  operator
are optional.

For numeric comparisons, the default tolerance  is  zero.
The tolerance can be altered with the tolerance command.

Text  constants  used  with  the  first  six   comparison
operators  are  right  padded  with  blanks  to match the
length of the field element on  the  other  side  of  the
comparison.   If a text constant is longer than the field
element, the query will be  rejected.   Text  comparisons
use  the ASCII collating sequence.  Hence, WHERE NAME>"B"
excludes NAMEs beginning with "A".

The $ and % operators test whether the left hand  element
contains  the  right  hand  element.  For example, NAME $
"Bee" is true for the Beethoven record but false for  the
other  composers.   The  %  operator returns the opposite
result.

The default table name in the where clause  is  inherited
from  the  qfn  list but is changed each time a qualified
field name is encountered.

Fields from different  tables  may  appear  in  the  same
comparison.   If  qfn's  from different tables are in the
display list, it is likely there will  be  at  least  one
cross-table  comparison.  For example, Figure 5-2 shows a
join of composers (COMP) with their works (OPUS).





                          5-13



















                    COMMAND REFERENCE
                         SELECT



      SELECT COMP.ALL OPUS.WORK WHERE COMPOSER=COMP.NAME


NAME                 BORN  DIED  COUNTRY   WORK

Brahms, J.           1833  1897  Germany   Lullaby
Beethoven, L.        1770  1827  Germany   Symphony No. 5

Figure 5-2.  Using select for a join.

The 11 records in COMP are paired with the 2 in OPUS  for
22  possible  combinations.   Of  those  22, only the two
which   have   identical   values   of   COMP.NAME    and
OPUS.COMPOSER  are displayed.  (This conceptual execution
of a join is not necessarily how PC-DBMS implements it.)

PC-DBMS permits up to ten comparisons to be used,  linked
together by ANDs and ORs.  For example, to find composers
who lived entirely in the nineteenth century, use

     SELECT COMP.ALL WHE BORN>1799 AND DIED<1900

The complement to this set of composers  is  the  set  of
composers  not  alive  at  any time during the nineteenth
century.  One way to find them  is  with  the  complement
operator, NOT.  For example,

     SELECT COMP.ALL WHE NOT ( BORN>1799 AND DIED<1900 )

NOT makes anything that is true false and vice versa.  It
is  typically  used  on  a  combination of comparisons as
above because the individual comparisons have  their  own
complements.   For  example,  NOT  NAME>"Smith"  is  just
NAME<= "Smith".  Space between NOT and ( is optional.

Parentheses, nested to any desired depth, can be used  to
alter  the order of evaluation of a where clause.  In the
preceding comparison, parentheses  assure  that  the  two
comparisons  are  evaluated  and  combined  and then that
result is negated.

                          5-14



















                    COMMAND REFERENCE
                         SELECT


Like algebra, there is an order of  evaluation  in  where
clauses.   Comparisons  are evaluated (to true and false)
first.  Then NOTs are applied.  AND comes next and OR  is
last.

A table may be referenced  for  the  first  time  in  the
command in the where clause.

The sorted by clause is also  optional.   It  contains  a
list  of  one  to five field names from tables referenced
earlier in the command.  The  first  field  name  is  the
major  sort  key  and  the  last  is  the minor sort key.
Filler fields may not be used.

The default sorting order is ascending (aardvarks  before
zebras).   To  get  descending  order on a key, follow it
with "\d" or "\D".  Use "\a" or  "\A"  to  get  ascending
order, the default.

For example, Figure 5-3 shows retrieval of  composers  in
reverse  order of birth and breaks ties (Bach and Handel)
by alphabetizing their names.

        SEL COMP.ALL SOR BY BORN\D NAME


NAME                 BORN  DIED  COUNTRY

Debussy, C.          1862  1918  France
Tchaikovsky, P.      1840  1893  Russia
Brahms, J.           1833  1897  Germany
Verdi, G.            1813  1901  Italy
Chopin, F.           1810  1849  France
Mendelssohn, F.      1809  1847  Germany
Beethoven, L.        1770  1827  Germany
Mozart, W.           1756  1791  Austria
Haydn, F.            1732  1809  Austria
Bach, J.             1685  1750  Germany
Handel, G.           1685  1759  England

Figure 5-3.  A select command with sorting.

                          5-15



















                    COMMAND REFERENCE
                         SELECT


The select command will abort with an  error  message  if
the  output  string  is  too  wide.   The  output  string
contains the fields  to  be  displayed  plus  two  spaces
between  adjacent  fields.   Spaces are suppressed if the
into clause is present.  Add to the output  string  width
the  width  of  the  widest  sort field.  The sum may not
exceed 141.

The optional  into  clause  comes  last.   It  makes  the
retrieved  table  part  of  the  data base by writing the
output to a file and making appropriate  entries  in  the
schema.  Only an information message will be displayed on
screen.

Into is followed by the name for  the  new  table.   That
name  follows  the  conventions for naming tables but may
not exceed eight characters.  The name may not already be
in use as a table name.  The file for the table will have
the table's  name  as  its  file  name  and  DAT  as  its
extension.   It  will  be in the current directory of the
logged drive.  No drive will be recorded for  it  in  the
schema.  The new table will have no indices.

No more than two tables can be referenced  prior  to  the
into clause.

An example of  a  complex  select  command  follows.   It
illustrates simultaneous use of all the clauses.

        SELECT COMP.COUNTRY COMP.BORN OPUS.ALL
         WHERE COMPOSER=COMP.NAME
         AND NOT (DIED<1900 AND BORN>1799)
         SORT BY NAME BORN\D
         INTO NU_TABLE

During output, scrolling can be suspended by pressing the
control-num lock or control-S key and resumed by pressing
any other key.  The F10 key aborts output.




                          5-16



















                    COMMAND REFERENCE
                         SELECT


Skeletal select commands can be composed easily with  the
DISPLAY\QUERY command.



SHOW

The show command displays a portion of the  schema.   The
syntax is:

     SHOW [\FIELDS] [\DESCRIBE] [table_name ...]

The optional qualifiers \FIELDS and  \DESCRIBE  determine
what  kind  of  information is retrieved from the schema.
It is possible to use zero, one or two qualifiers.   Note
that   SHOW\FIELDS\DESCRIBE   has   the  same  effect  as
SHOW\DESCRIBE\FIELDS.

The FIELDS  qualifier  displays  information  on  fields.
Without   this   qualifier,   information  on  tables  is
retrieved.   The  DESCRIBE  qualifier  retrieves   verbal
descriptions.     Without    this    qualifier,   tabular
information is retrieved.

If one or more tables is named, only information on those
tables  is  retrieved.   Otherwise, data on all tables is
shown.

Four   examples   follow.    They   show   all   possible
combinations  of  the  qualifiers  (one, the other, both,
neither).  For brevity, schema information  is  retrieved
for only one table.

The unqualified show command produces tabular information
on one or more tables.  See Figure 5-4.







                          5-17



















                    COMMAND REFERENCE
                          SHOW



        SHOW COMP  'info on table COMP

TABLE NAME  FILE           RECORDS FIELDS  EOR  MODIFIED

COMP        COMPOSER.DAT        11      6    2   01-11-85

Figure 5-4.  The show command without qualifiers.

The output exhibits the number  of  records  and  fields.
EOR  gives  the  end  of  record  terminator;  0 means no
terminator, 1 means carriage return and 2 means  carriage
return-line feed.

With just the DESCRIBE qualifier, show displays a  verbal
description of one or more tables.  See Figure 5-5.

        SHO\DESCRIBE COMP  'description of table COMP

   TABLE NAME          DESCRIPTION

COMP             information about some composers

Figure 5-5.  The show\describe command.

With just the FIELDS  qualifier,  show  displays  tabular
information  about the fields of one or more tables.  See
Figure 5-6.














                          5-18



















                    COMMAND REFERENCE
                          SHOW



        SHO\FIELDS COMP  'info on fields in table COMP

   FIELD NAME   TYPE KEY FROM    TO


TABLE   COMP

NAME              T   N     1    19
BORN              N   N    20    23
filler1           F   N    24    24
DIED              N   N    25    28
filler2           F   N    29    29
COUNTRY           T   N    30    44

Figure 5-6.  The show\fields command.

The TYPE column indicates the field's data type:   T  for
text,  N  for numeric and F for filler.  KEY indicates if
the field is indexed:  Y for yes and N for no.  The  FROM
and  TO  columns  show  each  field's starting and ending
positions in the data file.

Show with both qualifiers (either order) exhibits  verbal
descriptions  of  the  fields of one or more tables.  See
Figure 5-7.
















                          5-19



















                    COMMAND REFERENCE
                          SHOW



        SHO\FIE\DES COMP  'descriptions of fields in COMP

  FIELD NAME         DESCRIPTION


TABLE   COMP

NAME          the composer's last name and first initial
BORN          the year the composer was born
filler1
DIED          the year of the composer's death
filler2
COUNTRY       the composer's country of residence

Figure 5-7.  The show\fie\des command.

During output, scrolling can be suspended by pressing the
control-num lock or control-S key and resumed by pressing
any other key.  The F10 key aborts output.



TOLERANCE

The tolerance command sets the comparison tolerance  used
in  processing  where clauses.  It affects only numerical
comparisons.  The syntax is

     TOLERANCE number

The tolerance is set to the  absolute  value  of  number.
The  default  value  is zero, i.e., the value before this
command is first given.

Slight conversion  errors  occur  in  converting  between
decimal  (used  by people and stored in files) and binary
(used internally by PC-DBMS) because the machine does not
have  infinite precision.  Non-zero tolerance circumvents
these errors.


                          5-20



















                    COMMAND REFERENCE
                        TOLERANCE


The range  created  by  tolerance  can  be  exploited  to
simplify  queries.   For  example,  with  zero tolerance,
certain records are qualified by

     WHERE YEAR>=1895 AND YEAR<=1905

If tolerance is set to 5, the same records will, ignoring
conversion errors, be qualified by

     WHERE YEAR=1900

Here are the formal tolerance rules.  If  F  is  a  field
with  value  a and r is another field or a user specified
number and t is the tolerance,  then  the  following  are
true conditions.

        F =  r if and only if r-t <= a <= r+t
        F <> r if and only if a < r-t or a > r+t
        F >  r if and only if a > r-t
        F >= r if and only if a >= r-t
        F <  r if and only if a < r+t
        F <= r if and only if a <= r+t

If NOT precedes a where clause condition, the operator of
the  condition  will  be complemented and the NOT removed
when the condition is processed.  For example, NOT YEAR >
1900  is  processed  as YEAR <= 1900.  If tolerance is 2,
then records  with  YEAR  greater  than  1898  should  be
excluded.   In fact, records with YEAR less than or equal
to 1902 will be included.












                          5-21































                       APPENDIX A

                        KEYWORDS



PC-DBMS keywords  can  be  truncated  to  three  or  more
characters  and  entered  in  upper, lower or mixed case.
The keywords and their truncations should not be used for
names  of  data  bases,  tables  or  fields.   A  list of
keywords is in Table A-1.



ALL                INTO
ALLF               KEY
AND                OPEN
BY                 OR
DEFINE             OUTPUT
DEKEY              QUERY
DESCRIBE           REMOVE
DISPLAY            SELECT
EDIT               SHOW
EXIT               SORTED
FIELDS             TOLERANCE
INPUT              WHERE

Table A-1.  The keywords of PC-DBMS.






                           A-1































                       APPENDIX B

                  THE RELATIONAL MODEL



PC-DBMS is a data base management system which implements
the  relational  model of data management.  This appendix
describes the relational model and  how  to  design  data
bases around it.

Relational data bases contain  data  tables.   These  are
familiar  objects;   they  have  rows  called records and
columns called fields.

Data bases typically have more than  one  table.   For  a
given  information  content,  a data base might have one,
two, three  or  more  tables.   How  many  tables  should
contain  that  information?   Which  fields should be put
into which tables?  The purpose of this  appendix  is  to
help you answer those questions, i.e., to help you design
your  data  base.   If  you're  still  looking  for  more
information  when  you  finish,  you might consult C.  J.
Date, "An Introduction to  Database  Systems,"  (Reading,
Massachusetts:  Addison-Wesley Publishing Co., 1976).

We will progress through several data bases, each  better
organized than the last.

First, consider the one table data base in Figure B-1.




                           B-1



















                  THE RELATIONAL MODEL




NAME_1   SSNO_1        NAME_2   SSNO_2

Jack     123-45-6789   Mary     987-65-4321

Figure B-1.  A table with repeating fields.

There are four fields and one record.  It is obvious that
the fields repeat;  the second pair of fields contain the
same sort of information as the first pair.  This kind of
table is called unnormalized.

Suppose we have to find Mary's  Social  Security  number.
We  would  have to search the NAME_1 field and the NAME_2
field for "Mary".  She might be in either one.  This is a
difficult  query.   More  difficult still is a join, that
is, connecting the records in this table with the records
in  another  table  on  the  basis of a common field like
Social  Security  number.   Each  record  has  two   such
numbers.  Which one should be used?

Unnormalized tables can be normalized simply.  Just  make
additional  records  of the repeating fields as in Figure
B-2.

NAME   SSNO

Jack   123-45-6789
Mary   987-65-4321

Figure B-2.  A normalized table.

Normalized tables are said to be  in  first  normal  form
(1NF).

Let's define a concept known as  the  primary  key  of  a
table.   The  primary  key  is  a field or combination of
fields which, when their values are  specified,  uniquely
identifies  no  more  than one record.  For example, in a
table with the two  fields  SSNO  and  BIRTH_PLACE,  SSNO
would  be  the primary key because for each listed Social

                           B-2



















                  THE RELATIONAL MODEL



Security  number  there  is   only   one   birth   place.
BIRTH_PLACE cannot be the primary key because for a given
BIRTH_PLACE there may be  many  Social  Security  numbers
(people).

If a table has a primary key, its records are unique.

It may take several fields to  comprise  a  primary  key.
For  example,  in  a table which gives different people's
incomes for different years, there are the  fields  SSNO,
YEAR  and  INCOME.   The  first two uniquely identify the
record;  they are the primary key.

Let's append a fourth field to this two field primary key
table  to  study  second normal form (2NF).  The table is
already in 1NF because its records do not  contain  lists
or repeating fields.  See Figure B-3.

SSNO         YEAR  INCOME  NAME

123-45-6789  1980   15000  Jack
987-65-4321  1980   22000  Mary
123-45-6789  1981   16500  Jack
987-65-4321  1981   24000  Mary

Figure B-3.  A table not in 2NF.

Once both SSNO and YEAR are specified, the record can  be
identified  and  the other two fields looked up.  Neither
field in the primary key is sufficient to identify INCOME
uniquely.   For  example,  YEAR  =  1980  identifies  two
records.   The  entire  primary  key,  both  fields,   is
necessary to identify INCOME.

However, once SSNO is known, NAME can  be  looked  up  in
Figure  B-3.   That is, NAME depends on less than all the
fields in the primary key.  In relational jargon,  INCOME
is  fully  functionally  dependent on the primary key but
NAME is only functionally dependent.



                           B-3



















                  THE RELATIONAL MODEL



If all the non-key  fields  in  a  1NF  table  are  fully
functionally  dependent  on the primary key, the table is
in 2NF.  Why should we care?

Data base corruption is one reason we should  care.   For
example,   suppose  in  entering  "Mary"  twice,  it  was
misspelled once as "Marie".  Then,  one  Social  Security
number  would  correspond  to  two NAMEs.  Note that full
functional dependency implies only one value per  non-key
field  (INCOME)  for  each  primary key value.  Incorrect
replicates (Mary and Marie) cannot exist because multiple
entries   per   primary   key   are   precluded.   Hence,
consistency or integrity is one reason for 2NF.

There are other reasons for preferring 2NF but  let's  go
on  to  correct  the  problem.  Split one table into two.
See Figure B-4.

SSNO         YEAR  INCOME               SSNO         NAME

123-45-6789  1980   15000               123-45-6789  Jack
987-65-4321  1980   22000               987-65-4321  Mary
123-45-6789  1981   16500
987-65-4321  1981   24000

Figure B-4.  Tables in 2NF.

Both tables are in 2NF.  In the left table, SSNO and YEAR
are  the  primary  key  and  INCOME is fully functionally
dependent on them.   In  the  right  table  SSNO  is  the
primary  key  and  NAME  is  the  non-key field (but full
functional dependency is immaterial because  of  the  one
field primary key).

Note that in the two table version of the data  base,  we
can  record  Fred's  Social Security number (right table)
although there isn't enough information to make an  entry
for him in the left table.  This capability did not exist
in the single table (1NF) version.



                           B-4



















                  THE RELATIONAL MODEL



The table in Figure B-5  is  in  2NF  but  it  still  has
problems.

SSNO         AGE  HOME_TOWN  AVG_TEMP

123-45-6789   32  Boston           53
987-65-4321   19  Dallas           59
369-47-5814   50  El Paso          59
421-63-9631   41  Boston           47

Figure B-5.  A table not in 3NF.

SSNO is the primary  key  of  this  table.   The  non-key
fields,  AGE,  HOME_TOWN  and  AVG_TEMP, are functionally
dependent  on  the  primary   key.    (They   are   fully
functionally  dependent,  too,  but this is trivial for a
one field primary key.)

Observe that Boston has two average temperatures and they
are  different.   True,  this  illustrates  a  data entry
error.  However, it is possible to structure a data  base
to  guard  against such errors.  That's what third normal
form (3NF) is for.

In Figure B-5, there is a functional  dependency  between
non-key  fields.   That  is,  AVG_TEMP  is  dependent  on
HOME_TOWN.  Once HOME_TOWN is known, an uncorrupted  data
base  would  tell  us AVG_TEMP.  Dependency among non-key
fields is permissible in 2NF but not in 3NF.

The remedy, as before, is to split one  table  into  two.
See Figure B-6.

SSNO         AGE  HOME_TOWN           HOME_TOWN  AVG_TEMP

123-45-6789   32  Boston              Boston           53
987-65-4321   19  Dallas              Dallas           59
369-47-5814   50  El Paso             El Paso          59
421-63-9631   41  Boston

Figure B-6.  Tables in 3NF.

                           B-5



















                  THE RELATIONAL MODEL



In each new table of Figure B-6, the left  field  is  the
primary  key.   In the left table, the two non-key fields
are mutually  independent  --  knowing  either  does  not
identify  the  other.   Hence,  the left table is in 3NF.
There is only one non-key field in the right table so 2NF
implies 3NF.

Aside from avoiding the corruption  encountered  earlier,
it  is  also  possible  to  record  the  AVG_TEMP  for  a
HOME_TOWN even if we don't know the SSNO  of  anyone  who
lives  there.  That is, we can have a record in the right
table  without  a  correspondent  in  the   left   table.
Similarly, we can have a record in the left table even if
we don't know the AVG_TEMP of that person's HOME_TOWN.

To sum up, a table in  1NF  has  no  lists  or  repeating
fields  in  its  records.   A 2NF table is a 1NF table in
which each non-key field is fully functionally  dependent
on the entire primary key.  Finally, a 3NF table is a 2NF
table  in  which  the   non-key   fields   are   mutually
independent.

A data  base  consisting  of  third  normal  form  tables
provides the most protection against corruption.  It also
permits tables to  be  modified  individually,  e.g.,  we
don't have to know the age and Social Security of someone
in Detroit to enter that city's  average  temperature  in
Figure B-6.














                           B-6































                       APPENDIX C

                 USER SUPPORTED SOFTWARE



PC-DBMS is distributed as user supported  software.   You
are   encouraged  to  make  copies  of  the  distribution
diskette for other potential users.  There is no  license
agreement   but  please  pass  along  all  files  without
modification.  User groups and similar organizations  may
impose a nominal charge to cover the cost of duplication.

The user supported concept of software is  a  significant
innovation  (not  mine)  which  benefits  both  user  and
author.  For  the  user,  there  is  the  convenience  of
leisurely evaluation of the program on your own system in
your own home or office to see if  it's  right  for  your
application.   The  low  cost  of user supported software
distribution also  makes  possible  a  voluntary  payment
lower  than  the  price  of commercial software.  And, of
course,  there's  no  copy  protection  to  diminish  the
utility of the program.

For the author, there is the satisfaction  of  getting  a
far  larger  number  of  copies into use than is possible
with conventional marketing.  Also, the  interference  of
piracy is turned into a reinforcement in the distribution
of the program.   Finally,  if  you  have  dealt  with  a
publisher,   print  or  electronic,  you  understand  the
undesirability of that alternative.



                           C-1



















                 USER SUPPORTED SOFTWARE



However, it doesn't work unless you want it to.   If  you
find  PC-DBMS  useful,  I hope you will see fit to make a
voluntary payment of $60, by check or through  your  bank
credit  card.  Beyond what you already have, this is what
you will receive from me.

      -  A typeset, bound edition of the User's Guide.

      -  A mailed copy of the  next  version  of  PC-DBMS
         without charge.

      -  Support.  Comments, suggestions  and  especially
         bug  reports  are welcome from anyone.  However,
         only users who have made a voluntary payment can
         expect replies to their questions.  Support will
         be provided on a best efforts basis by mail  and
         through   Compuserve.    My   Compuserve  id  is
         74216,3033.

      -  My thanks.  PC-DBMS took a year to develop, test
         and document.  I enjoyed every minute of it.


Now, I ask that you send your voluntary payment with  the
registration  form.   A  copy of that form is on the next
page or, if you wish to leave your manual intact, another
copy is in the file REQUEST.DOC.  If you need an invoice,
you'll find one following the registration form  in  both
the manual and in REQUEST.DOC.

For quantity purchases of PC-DBMS, contact Kware.

Thank you.









                           C-2



















                PC-DBMS REGISTRATION FORM


Name    _____________________________  Phone ____________

Company _____________________________

Address _____________________________

        _____________________________

City    _____________________________

State   ______________ Zip __________

version number  ___________


source of your copy (club or bulletin board),

  if known  _____________________________________________


To use a credit card, please indicate:

  Visa  __   or Mastercard __

  card number  ____________________________

  expiration month/year  ______/________

  signature  ______________________________


To pay by check, please make payable to Kware for $60.
  Virginia residents add 4% sales tax.


Kware                               Thank you.
P. O. Box 16206
Arlington, Va. 22215                  Paul Kobrin





                           C-3





















                       INVOICE


Vendor:  Kware, Inc.              Federal EIN: 54-1304150
         P. O. Box 16206
         Arlington, Va. 22215     Date:    /    /


Description        Qty      Price       Extension
-----------        ---      -----       ---------

PC-DBMS,             1      $60.00         $60.00
 User's Guide,
 support
                                        ---------
                   Sub-total                60.00

Tax @ 4% for Va. residents ($2.40)

                                        ---------
                   Amount due



You may retain this invoice.
Please make check payable to Kware.


















                           C-4



















                          INDEX




All
  keyword  . . . . . . . . . 3-5, 5-5, 5-11
Allf
  keyword  . . . . . . . . . 5-11
Alphabetize  . . . . . . . . 3-6, 5-15
And
  keyword  . . . . . . . . . 3-6, 5-5, 5-12, 5-14 to 5-15
Apostrophe
  comment delimiter  . . . . 4-5
Ascii  . . . . . . . . . . . 4-2, 5-13

Backslash
  in deleted records . . . . 4-2, 5-3
Backspace key  . . . . . . . 3-4, 3-6, 3-13, 4-4
Blank padding  . . . . . . . 5-13
Brackets . . . . . . . . . . 5-2

Carriage return  . . . . . . 3-4, 4-4
Case
  sensitivity  . . . . . . . 4-3, 5-2, A-1
Character string . . . . . . 4-5
  see also text string
Close command  . . . . . . . 5-9
Color  . . . . . . . . . . . 2-1, 3-2
Command window . . . . . . . 3-4, 4-3, 4-5, 5-6
Comments
  in commands  . . . . . . . 4-5
Comparison . . . . . . . . . 5-12 to 5-14, 5-20
  see also condition
Compuserve . . . . . . . . . C-2
Condition  . . . . . . . . . 3-5, 5-12, 5-21
  see also comparison
Continuation character . . . 4-4
Control-num lock . . . . . . 5-16, 5-20
Control-s  . . . . . . . . . 3-7, 3-9, 5-16, 5-20
Cursor keys  . . . . . . . . 3-4, 3-11, 3-13, 4-4

Data dictionary  . . . . . . 3-10
  see also schema
Default table  . . . . . . . 3-7 to 3-8, 5-7, 5-11, 5-13

                         Index-1



















Define command . . . . . . . 3-12, 5-2
Define utility . . . . . . . 3-12, 3-15, 4-2, 5-2
Dekey command  . . . . . . . 3-15 to 3-16, 5-4
Delete key . . . . . . . . . 3-6, 4-4
Directory  . . . . . . . . . 4-1, 5-16
  see also subdirectory
Display command  . . . . . . 3-10, 5-4, 5-7, 5-9
Display utility  . . . . . . 3-10 to 3-11, 3-15, 4-3,
                             5-3, 5-5 to 5-8
Distribution diskette  . . . 2-1
Dos  . . . . . . . . . . . . 2-1, 2-3, 3-2, 3-16, 5-3
Drive names  . . . . . . . . 5-3

Editor . . . . . . . . . . . 4-3, 5-8
Ellipses . . . . . . . . . . 5-2
End key  . . . . . . . . . . 4-4
Errors
  see messages
Exit command . . . . . . . . 3-16, 4-5, 5-8
Export
  data . . . . . . . . . . . 4-2

F1 key . . . . . . . . . . . 3-3, 3-11, 4-5, 5-8
F10 key  . . . . . . . . . . 3-7, 3-11 to 3-12,
                             3-15 to 3-16, 4-5, 5-3,
                             5-5 to 5-7, 5-16, 5-20
F2 key . . . . . . . . . . . 4-6
F3 key . . . . . . . . . . . 3-4, 4-5
F4 key . . . . . . . . . . . 3-12, 5-6
F6 key . . . . . . . . . . . 3-12, 5-6
F7 key . . . . . . . . . . . 3-4, 3-11 to 3-12, 4-5,
                             5-5 to 5-6
F9 key . . . . . . . . . . . 3-11, 4-5, 5-8
Field block  . . . . . . . . 3-10 to 3-11, 4-3, 5-4, 5-8
Fields
  filler . . . . . . . . . . 3-9, 5-3, 5-13, 5-15
  functionally dependent . . B-3 to B-6
  numeric  . . . . . . . . . 3-9, 5-3
  text . . . . . . . . . . . 3-9, 5-3
File specifications  . . . . 5-3
Files
  character positions in . . 3-9
  data . . . . . . . . . . . 4-1 to 4-2, 5-2, 5-16, 5-19
  def  . . . . . . . . . . . 4-1
  distribution . . . . . . . 2-1, C-1

                         Index-2



















  idx  . . . . . . . . . . . 4-1
  index  . . . . . . . . . . 4-1, 5-4, 5-8, 5-10
  maintenance  . . . . . . . 3-15
  schema . . . . . . . . . . 4-1 to 4-2, 5-9
Flying reform
  editor . . . . . . . . . . 4-3 to 4-4
Functionally dependent
  see fields

Help . . . . . . . . . . . . 3-3, 3-11, 4-5 to 4-6, 5-8
Help menu  . . . . . . . . . 3-3
Home key . . . . . . . . . . 4-4

Import
  data . . . . . . . . . . . 4-2, 5-3
Index  . . . . . . . . . . . 3-15 to 3-16, 5-4,
                             5-8 to 5-9, 5-16
  see also key
Insert mode
  editor . . . . . . . . . . 4-3
Installation . . . . . . . . 2-2
Into clause  . . . . . . . . 3-7 to 3-8, 3-15, 5-10, 5-16
Invoice  . . . . . . . . . . C-4

Join
  relational operation . . . 3-7 to 3-8, 3-16,
                             5-13 to 5-14, B-2

Key  . . . . . . . . . . . . 3-15 to 3-16, 5-19
  see also index
Key command  . . . . . . . . 3-15, 5-8
Keyword  . . . . . . . . . . 4-3, A-1
  see also truncation

Main screen  . . . . . . . . 3-2, 3-11 to 3-13, 3-15,
                             4-3, 4-5, 5-5 to 5-6
Menu
  see help menu
Message window . . . . . . . 3-4
Messages
  error or warning . . . . . 3-4, 4-5

Names
  data base  . . . . . . . . 4-3, A-1
  field  . . . . . . . . . . 4-3, A-1

                         Index-3



















  qualified field  . . . . . 3-7, 4-3, 5-4, 5-9, 5-11,
                             5-13
  table  . . . . . . . . . . 4-3, A-1
    into clause  . . . . . . 5-16
Normal form  . . . . . . . . B-2 to B-3, B-5 to B-6
Not
  keyword  . . . . . . . . . 5-14 to 5-15, 5-21
Numbers
  representation . . . . . . 4-4

Open command . . . . . . . . 3-4, 5-9
Or
  keyword  . . . . . . . . . 3-6, 5-12, 5-14 to 5-15
Output command . . . . . . . 5-9 to 5-10
Output window  . . . . . . . 3-2

Parentheses  . . . . . . . . 5-14
Path names . . . . . . . . . 5-3
Primary key  . . . . . . . . B-2 to B-6
Printer  . . . . . . . . . . 5-9

Qualified field names
  see names
Quote mark
  text delimiter . . . . . . 3-6, 4-5, 5-7

Recall
  command  . . . . . . . . . 3-8
Record terminator  . . . . . 5-3, 5-18
Relational model . . . . . . B-1
Remove command . . . . . . . 3-10, 4-2, 5-10
Replace mode
  editor . . . . . . . . . . 4-3 to 4-4, 5-8

Schema . . . . . . . . . . . 3-10, 3-12, 4-1 to 4-2, 5-4,
                             5-8 to 5-10, 5-16 to 5-17
Scroll . . . . . . . . . . . 3-6 to 3-7, 3-9, 5-20
Select command . . . . . . . 3-5, 3-7 to 3-8, 4-3, 5-3,
                             5-5, 5-9 to 5-11
Show command . . . . . . . . 3-8, 3-10, 5-9, 5-17
Sort clause  . . . . . . . . 3-6, 5-3, 5-15
  maximum width  . . . . . . 5-16
Space  . . . . . . . . . . . 4-4, 5-13 to 5-14
Subdirectory . . . . . . . . 2-3, 3-2
  see also directory

                         Index-4



















Support  . . . . . . . . . . C-2

Tab key  . . . . . . . . . . 3-11, 5-8
Text string  . . . . . . . . 3-6
  see also character string
Tolerance command  . . . . . 5-13, 5-20
Truncation
  keyword  . . . . . . . . . 3-8, 4-5, 5-2, A-1

User supported software  . . C-1

Warnings
  see messages
Welcome screen . . . . . . . 3-2
Where clause . . . . . . . . 3-5 to 3-6, 3-16, 5-3, 5-5,
                             5-7, 5-12 to 5-13
  order of evaluation  . . . 5-15
Word wrapping
  editor . . . . . . . . . . 3-4, 4-3



























                         Index-5